﻿using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;

namespace DAL
{
    public class ContractDal
    {
        ModelContractDbContext dbContext = new ModelContractDbContext();
        public List<hetongjbxx> GetHetongjbxxes()
        {
            return dbContext.hetongjbxx.ToList<hetongjbxx>();
        }

        public hetongjbxx GetHetongjbxx(int id)
        {
            return dbContext.hetongjbxx.Where(u=>u.id==id).FirstOrDefault();
        }

        public List<hetong_chanpin> GetHetongchanpins(string hetongbh)
        {
            return dbContext.hetong_chanpin.Where(u => u.hetongbh == hetongbh).ToList();
        }

        public DataSet GenerateContract(int id)
        {
            hetongjbxx jbxx= GetHetongjbxx(id);
            string sql = "SELECT * FROM hetongjbxx where id=@id";
            string sql2 = "SELECT hetongbh,chanpinmc,xinghao,pinpai,chandi,danjia,danwei,shuliang,xiaoji FROM hetong_chanpin where hetongbh=@hetongbh";
            DBHelper db = new DBHelper();
            SqlParameter[] sqlParameters = new SqlParameter[] 
            { 
                new SqlParameter(){ParameterName="@id", DbType=DbType.Int32,Value=id}
            };
            DataTable table = db.ExecuteGetDataTable(CommandType.Text, sql, sqlParameters);
            table.TableName = "Contract";
            SqlParameter[] sqlParameters2 = new SqlParameter[]
            {
                new SqlParameter(){ParameterName="@hetongbh", DbType=DbType.String,Value=jbxx.hetongbh}
            };
            DataTable table2 = db.ExecuteGetDataTable(CommandType.Text, sql2, sqlParameters2);
            //一定注意
            table2.TableName = "Product";
            // 从数据库获取数据
            DataSet ds = new DataSet();
            ds.Tables.Add(table);
            ds.Tables.Add(table2);
            DataRelation dataRelation = new DataRelation("Product", table.Columns["hetongbh"], table2.Columns["hetongbh"]);
            ds.Relations.Add(dataRelation);
            return ds;
        }
    }
}
